package util
@Grab('org.hsqldb:hsqldb:2.3.2')
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql

import java.util.logging.Level
import java.util.logging.Logger

class DbUtil {
    static Sql create() {
        def url = 'jdbc:hsqldb:mem:GinA'
        def user = 'sa'
        def password = ''
        def driver = 'org.hsqldb.jdbcDriver'
        def sql = Sql.newInstance(url, user, password, driver)

        sql.execute """
            DROP TABLE Athlete IF EXISTS cascade;
            DROP TABLE Record IF EXISTS;
        """

        sql.execute """
            CREATE TABLE Athlete (
              athleteId   INTEGER GENERATED BY DEFAULT AS IDENTITY,
              firstname   VARCHAR(64),
              lastname    VARCHAR(64),
              dateOfBirth DATE,
              UNIQUE(athleteId)
            );
        """

        sql.execute '''
CREATE TABLE Record (
  recordId    INTEGER GENERATED BY DEFAULT AS IDENTITY,
  time        INTEGER,    -- in seconds
  venue       VARCHAR(64),
  whenRun     DATE,
  fkAthlete   INTEGER,
  CONSTRAINT fk FOREIGN KEY (fkAthlete)
    REFERENCES Athlete (athleteId) ON DELETE CASCADE
);
        '''
        sql
    }

    static void populate(sql) {
        insertAthlete(sql, 'Paul', 'Tergat', '1969-06-17')
        insertAthlete(sql, 'Khalid', 'Khannouchi', '1971-12-22')
        insertAthlete(sql, 'Ronaldo', 'da Costa', '1970-06-07')
        insertRecord(sql, 2, 4, 55, 'Berlin', '2003-09-28', 'Tergat')
        insertRecord(sql, 2, 5, 38, 'London', '2002-04-14', 'Khannouchi')
        insertRecord(sql, 2, 5, 42, 'Chicago', '1999-10-24', 'Khannouchi')
        insertRecord(sql, 2, 6, 05, 'Berlin', '1998-09-20', 'da Costa')
    }

    static void insertAthlete(sql, first, last, dob) {
        sql.execute """
            INSERT INTO Athlete (firstname, lastname, dateOfBirth)
            VALUES (${first},${last},${dob})
        """
    }

    static void insertRecord(sql, h, m, s, venue, date, lastname) {
        def time = h * 60 * 60 + m * 60 + s
        sql.execute """
            INSERT INTO Record (time, venue, whenRun, fkAthlete)
              SELECT $time, $venue, $date,
                athleteId FROM Athlete WHERE lastname=$lastname
        """
    }

    // To enable logging, call the method below. You might also need
    // to adjust the logging.properties file in JRE_HOME/lib to have:
    // java.util.logging.ConsoleHandler.level = FINE
    static void enableLogging() {
        Logger.getLogger('groovy.sql').level = Level.FINE
    }
}
